ALTER FUNCTION [dbo].[CalInterestWkly]
(
	@report_date DATETIME,
	@principal DECIMAL(18, 2),
	@interest_rate DECIMAL(18, 8),
	@loan_start_date DATETIME,	
	@term INTEGER,	
	@payment_amount DECIMAL(18, 2)	
)
RETURNS DECIMAL(18, 2)
AS
BEGIN

	DECLARE @interest DECIMAL(18, 8) = 0,
		@wkly_interest DECIMAL(18, 8) = 0,	
		@payment_date DATETIME = DATEADD(wk, 1, @loan_start_date),
		@loan_end_date DATETIME = DATEADD(week, @term, @loan_start_date),
		@num_days INTEGER,	
		@month_end DATETIME = @loan_start_date
	
	SET @interest_rate = (@interest_rate / 100) / 52
	
	IF @loan_start_date < @report_date AND (@loan_end_date > @report_date OR (YEAR(@loan_end_date) = YEAR(@report_date) AND MONTH(@loan_end_date) = MONTH(@report_date)))
	BEGIN
		WHILE @payment_date <= @report_date
		BEGIN
			IF YEAR(@loan_start_date) = YEAR(@report_date) AND (MONTH(@loan_start_date) = MONTH(@report_date) OR MONTH(@month_end) = MONTH(@report_date))
			BEGIN
				IF @report_date > @loan_end_date --ensure that the loan is not calculated pass the expiry date
				BEGIN
					SET @month_end = @loan_end_date
				END
				ELSE
				BEGIN
					SET @month_end = @report_date
				END
				WHILE @payment_date <= @month_end
				BEGIN
					SET @wkly_interest = @principal * @interest_rate
					SET @interest = @interest + @wkly_interest
					SET @principal = @principal + @wkly_interest - @payment_amount
					
					IF DATEADD(week, 1, @payment_date) > @month_end
					BEGIN
						SET @num_days = DATEDIFF(day, @payment_date, @month_end)
						SET @wkly_interest = @principal * ((@interest_rate * 52) / 365) * @num_days
						SET @interest = @interest + @wkly_interest
						SET @principal = @principal + @wkly_interest - @payment_amount
					END
				
					SET @payment_date = DATEADD(week, 1, @payment_date)
				END
			END
			ELSE
			BEGIN
				SET @month_end = DATEADD(day, DAY(DATEADD(mm, DATEDIFF(mm, -1, @month_end), -1)) - DAY(@month_end), @month_end)
				
				SET @interest = 0
				
				WHILE @payment_date <= @month_end
				BEGIN
					SET @wkly_interest = @principal * @interest_rate
					SET @interest = @interest + @wkly_interest
					SET @principal = @principal + @wkly_interest - @payment_amount
					SET @payment_date = DATEADD(week, 1, @payment_date)
				END
			END
		END
	END
	
	
	
	
	--IF @loan_start_date < @report_date AND (@loan_end_date > @report_date OR (YEAR(@loan_end_date) = YEAR(@report_date) AND MONTH(@loan_end_date) = MONTH(@report_date)))
	--BEGIN
	----	WHILE @month_end < @report_date
	----	BEGIN
	--		/*Check if the report is for the first month of the loan, for eg loan started n january and report is for january*/
	--		IF YEAR(@loan_start_date) = YEAR(@report_date) AND MONTH(@loan_start_date) = MONTH(@report_date)
	--		BEGIN
	--			IF @report_date > @loan_end_date --ensure that the loan is not calculated pass the expiry date
	--			BEGIN
	--				SET @month_end = @loan_end_date
	--			END
	--			ELSE
	--			BEGIN
	--				SET @month_end = @report_date
	--			END
	--		END
	--		ELSE --if the report is not for the first month of the loan, for eg loan started in january and report is for february
	--		BEGIN
	--			IF DATEADD(month, 1, @month_end) > @loan_end_date OR DATEADD(month, 1, @month_end) > @report_date--ensure that the loan is not calculated pass the expiry date
	--			BEGIN
	--				IF DATEADD(month, 1, @month_end) > @loan_end_date
	--				BEGIN
	--					SET @month_end = @loan_end_date
	--				END
	--				ELSE
	--				BEGIN
	--					SET @month_end = @report_date
	--				END
	--			END
	--			ELSE
	--			BEGIN
	--				SET @month_end = DATEADD(day, DAY(DATEADD(mm, DATEDIFF(mm, -1, @month_end), -1)) - DAY(@month_end), @month_end)
	--			END
	--			--SET @month_end = DATEADD(day, DAY(DATEADD(mm, DATEDIFF(mm, -1, @loan_start_date), -1)) - DAY(@loan_start_date), @loan_start_date)
	--		END
			
	--		WHILE @payment_date <= @month_end
	--		BEGIN
	--			SET @wkly_interest = @principal * @interest_rate
	--			SET @interest = @interest + @wkly_interest
	--			SET @principal = @principal + @wkly_interest - @payment_amount
				
	--			IF DATEADD(week, 1, @payment_date) > @month_end
	--			BEGIN
	--				SET @num_days = DATEDIFF(day, @payment_date, @month_end)
	--				SET @wkly_interest = @principal * ((@interest_rate * 52) / 365) * @num_days
	--				SET @interest = @interest + @wkly_interest
	--				SET @principal = @principal + @wkly_interest - @payment_amount
	--			END
				
	--			SET @payment_date = DATEADD(week, 1, @payment_date)
	--		END
			
	--	END
		
		--IF YEAR(@loan_start_date) = YEAR(@report_date) AND MONTH(@loan_start_date) = MONTH(@report_date)
		--BEGIN
		--	SET @num_days = DATEDIFF(day, @loan_start_date, @report_date)			
		--END
	--	ELSE
	--	BEGIN
	--		SET @num_days = DATEDIFF(day, @loan_start_date, @month_end)	
	--	END		
		 
				
	--	SET @interest = @principal * @daily_interest_rate * @num_days
	--	SET @principal = @principal + @interest - @payment_amount 
	--	SET @month_end = DATEADD(month, 1, @month_end)
		
		
	--	WHILE (YEAR(@month_end) < YEAR(@report_date) OR (YEAR(@month_end) = YEAR(@report_date) AND MONTH(@month_end) < MONTH(@report_date)))
	--	BEGIN
	--		SET @interest = @principal * @mnthly_interest_rate			
	--		SET @principal = @principal + @interest - @payment_amount
	--		SET @month_end = DATEADD(month, 1, @month_end)
	--	END	
		
	--	IF YEAR(@month_end) = YEAR(@report_date) AND MONTH(@month_end) = MONTH(@report_date)
	--	BEGIN
	--		IF @report_date <= @loan_end_date
	--		BEGIN
	--			SET @num_days = DAY(@report_date)
	--		END
	--		ELSE
	--		BEGIN
	--			SET @num_days = DAY(@loan_end_date)
	--		END
			
	--		SET @interest = @principal * @daily_interest_rate * @num_days
	--	END	
	--END
	--RETURN @interest
	
	

	--SET @interest_rate = (@interest_rate / 100) / 52

	--IF @payment_date < @report_date
	--BEGIN
	--	WHILE (YEAR(@payment_date) < YEAR(@report_date) OR (YEAR(@payment_date) = YEAR(@report_date) AND MONTH(@payment_date) < MONTH(@report_date)))
	--	BEGIN
	--		SET @wkly_interest = @principal * @interest_rate
	--		SET @interest = @interest + @wkly_interest
	--		SET @principal = @principal + @wkly_interest - @payment_amount
	--		SET @payment_date = DATEADD(wk, 1, @payment_date)
	--		SET @counter = @counter + 1
	--	END	

	--	SET @month_end = DATEADD(day, DAY(DATEADD(mm, DATEDIFF(mm, -1, @payment_date), -1)) - DAY(@payment_date), @payment_date)
	--	SET @interest = 0

	--	WHILE @payment_date <= @report_date
	--	BEGIN
	--		IF @counter <= @term
	--		BEGIN
	--			SET @wkly_interest = @principal * @interest_rate
	--			SET @interest = @interest + @wkly_interest
	--			SET @principal = @principal + @wkly_interest - @payment_amount
				
	--			SET @payment_date = DATEADD(wk, 1, @payment_date)
	--			IF @payment_date > @report_date
	--			BEGIN
	--				SET @wkly_interest = ((@interest_rate * 52) / 365 ) * @principal * DATEDIFF(day, @report_date, @payment_date)
	--				SET @interest = @interest + @wkly_interest
	--			END
	--			SET @counter = @counter + 1
	--		END	
	--		ELSE
	--		BEGIN
	--			BREAK
	--		END	
	--	END
	--END
	RETURN @interest
END;

